package com.yh.signinmongodb.service.impl;

import com.yh.signinmongodb.dao.StudentRepository;
import com.yh.signinmongodb.entity.Student;
import com.yh.signinmongodb.service.StudentService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentRepository studentRepository;

    @Autowired
    private MongoTemplate mongoTemplate;

    @Override
    public List<Student> selectStudent() {
        return studentRepository.findAll();
    }

    @Override
    public void updateStudentByStats(String name) {
        Query query = new Query(Criteria.where("name").is(name));
        Update update = new Update();
        update.set("state",1);
        mongoTemplate.updateFirst(query,update,Student.class);
    }

    @Override
    public void updateAllStats() {
        Query query = new Query(Criteria.where("state").is(1));
        Update update = new Update();
        update.set("state",0);
        mongoTemplate.updateMulti(query,update,Student.class);
    }

    @Override
    public void upload(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<Student> studentList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            Student student = new Student();
            if( row.getCell(0).getCellType() !=1){
                throw new Exception("导入失败(第"+(r+1)+"行,姓名请设为文本格式)");
            }
            String name = row.getCell(0).getStringCellValue();
            if(name == null || name.isEmpty()){
                throw new Exception("导入失败(第"+(r+1)+"行,姓名未填写)");
            }
            student.setName(name);
            student.setState(0);
            studentList.add(student);
        }
        mongoTemplate.insertAll(studentList);
    }

}
